                                                                      
ALTER TABLE project ADD COLUMN budget numeric;

-- DROP old SPs
DROP FUNCTION sp_project_insert(character varying, character varying, character varying, character varying, integer, integer, integer, boolean, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, integer, integer, integer);
DROP FUNCTION sp_project_selectlist(integer, integer, integer, character varying, character varying, character varying, character varying, integer, integer, integer, boolean, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, integer, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_project_count(integer, character varying, character varying, character varying, character varying, integer, integer, integer, boolean, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, integer, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_project_update(integer, character varying, character varying, character varying, character varying, integer, integer, integer, boolean, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, integer, integer, integer, boolean, timestamp without time zone);

-- NEW SPs
CREATE OR REPLACE FUNCTION sp_project_insert(
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_type	integer DEFAULT null,
	_pm	integer DEFAULT null,
	_id_company	integer DEFAULT null,
	_is_template	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_event_date	timestamp without time zone DEFAULT null,
	_plan_start_date	timestamp without time zone DEFAULT null,
	_plan_end_date	timestamp without time zone DEFAULT null,
	_real_start_date	timestamp without time zone DEFAULT null,
	_real_end_date	timestamp without time zone DEFAULT null,
	_attendees	integer DEFAULT null,
	_id_venue	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_avatar	character varying(255) DEFAULT null,
	_budget	numeric DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO project(name,code,description,id_type,pm,id_company,is_template,status,event_date,plan_start_date,plan_end_date,real_start_date,real_end_date,attendees,id_venue,creator,updator,deletion_time,avatar,budget)
	VALUES(_name,_code,_description,_id_type,_pm,_id_company,_is_template,_status,_event_date,_plan_start_date,_plan_end_date,_real_start_date,_real_end_date,_attendees,_id_venue,_creator,_creator,null,_avatar,_budget);
RETURN currval('project_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_type	integer DEFAULT null,
	_pm	integer DEFAULT null,
	_id_company	integer DEFAULT null,
	_is_template	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_event_dateFrom	timestamp without time zone DEFAULT null,
	_event_dateTo	timestamp without time zone DEFAULT null,
	_plan_start_dateFrom	timestamp without time zone DEFAULT null,
	_plan_start_dateTo	timestamp without time zone DEFAULT null,
	_plan_end_dateFrom	timestamp without time zone DEFAULT null,
	_plan_end_dateTo	timestamp without time zone DEFAULT null,
	_real_start_dateFrom	timestamp without time zone DEFAULT null,
	_real_start_dateTo	timestamp without time zone DEFAULT null,
	_real_end_dateFrom	timestamp without time zone DEFAULT null,
	_real_end_dateTo	timestamp without time zone DEFAULT null,
	_attendees	integer DEFAULT null,
	_id_venue	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_avatar	character varying(255) DEFAULT null,
	_budget	numeric DEFAULT null
)
RETURNS SETOF project
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM project WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_type is null OR id_type = _id_type)	
	AND (_pm is null OR pm = _pm)	
	AND (_id_company is null OR id_company = _id_company)	
	AND (_is_template is null OR is_template = _is_template)	
	AND (_status is null OR status = _status)	
	AND (_event_dateFrom is null OR event_date >= _event_dateFrom)
	AND	(_event_dateTo is null OR event_date <= _event_dateTo)	
	AND (_plan_start_dateFrom is null OR plan_start_date >= _plan_start_dateFrom)
	AND	(_plan_start_dateTo is null OR plan_start_date <= _plan_start_dateTo)	
	AND (_plan_end_dateFrom is null OR plan_end_date >= _plan_end_dateFrom)
	AND	(_plan_end_dateTo is null OR plan_end_date <= _plan_end_dateTo)	
	AND (_real_start_dateFrom is null OR real_start_date >= _real_start_dateFrom)
	AND	(_real_start_dateTo is null OR real_start_date <= _real_start_dateTo)	
	AND (_real_end_dateFrom is null OR real_end_date >= _real_end_dateFrom)
	AND	(_real_end_dateTo is null OR real_end_date <= _real_end_dateTo)	
	AND (_attendees is null OR attendees = _attendees)	
	AND (_id_venue is null OR id_venue = _id_venue)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_avatar is null OR avatar = _avatar)	
	AND (_budget is null OR budget = _budget)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_type	integer DEFAULT null,
	_pm	integer DEFAULT null,
	_id_company	integer DEFAULT null,
	_is_template	boolean DEFAULT null,
	_status	integer DEFAULT null,
	_event_dateFrom	timestamp without time zone DEFAULT null,
	_event_dateTo	timestamp without time zone DEFAULT null,
	_plan_start_dateFrom	timestamp without time zone DEFAULT null,
	_plan_start_dateTo	timestamp without time zone DEFAULT null,
	_plan_end_dateFrom	timestamp without time zone DEFAULT null,
	_plan_end_dateTo	timestamp without time zone DEFAULT null,
	_real_start_dateFrom	timestamp without time zone DEFAULT null,
	_real_start_dateTo	timestamp without time zone DEFAULT null,
	_real_end_dateFrom	timestamp without time zone DEFAULT null,
	_real_end_dateTo	timestamp without time zone DEFAULT null,
	_attendees	integer DEFAULT null,
	_id_venue	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_avatar	character varying(255) DEFAULT null,
	_budget	numeric DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM project WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_type is null OR id_type = _id_type)	
	AND (_pm is null OR pm = _pm)	
	AND (_id_company is null OR id_company = _id_company)	
	AND (_is_template is null OR is_template = _is_template)	
	AND (_status is null OR status = _status)	
	AND (_event_dateFrom is null OR event_date >= _event_dateFrom)
	AND	(_event_dateTo is null OR event_date <= _event_dateTo)	
	AND (_plan_start_dateFrom is null OR plan_start_date >= _plan_start_dateFrom)
	AND	(_plan_start_dateTo is null OR plan_start_date <= _plan_start_dateTo)	
	AND (_plan_end_dateFrom is null OR plan_end_date >= _plan_end_dateFrom)
	AND	(_plan_end_dateTo is null OR plan_end_date <= _plan_end_dateTo)	
	AND (_real_start_dateFrom is null OR real_start_date >= _real_start_dateFrom)
	AND	(_real_start_dateTo is null OR real_start_date <= _real_start_dateTo)	
	AND (_real_end_dateFrom is null OR real_end_date >= _real_end_dateFrom)
	AND	(_real_end_dateTo is null OR real_end_date <= _real_end_dateTo)	
	AND (_attendees is null OR attendees = _attendees)	
	AND (_id_venue is null OR id_venue = _id_venue)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_avatar is null OR avatar = _avatar)	
	AND (_budget is null OR budget = _budget);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_project_update 
(	_id integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying,
	_id_type integer,
	_pm integer,
	_id_company integer,
	_is_template boolean,
	_status integer,
	_event_date timestamp without time zone,
	_plan_start_date timestamp without time zone,
	_plan_end_date timestamp without time zone,
	_real_start_date timestamp without time zone,
	_real_end_date timestamp without time zone,
	_attendees integer,
	_id_venue integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_avatar character varying(255),
	_budget numeric  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE project 
	SET 		name = _name,
		code = _code,
		description = _description,
		id_type = _id_type,
		pm = _pm,
		id_company = _id_company,
		is_template = _is_template,
		status = _status,
		event_date = _event_date,
		plan_start_date = _plan_start_date,
		plan_end_date = _plan_end_date,
		real_start_date = _real_start_date,
		real_end_date = _real_end_date,
		attendees = _attendees,
		id_venue = _id_venue,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		avatar = _avatar,
		budget = _budget
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
